<?php

namespace App\Http\Controllers\UserCenter;

use DB;
use App\Eloquent\User;
use App\Http\Controllers\Controller;
use App\Extensions\Pagination\BoosstrapPageUser;
use Illuminate\Http\Request;
use Illuminate\Pagination\LengthAwarePaginator;

/**
 * 经纪人
 */
class AgentController extends Controller{

	public function init(){
		$user = User::user();
		if(!($user->hasRole('Agent')) && !empty($user->second_broker_code)){
			return abort(404);
		};	//校验用户是否是投资人角色
	}

	/**
	 * 客户管理
	 * 
	 * @return [type] [description]
	 */
	public function customerManagement(Request $request){
		if($query = $request->only('username', 'name','invite_user'))

		$user = User::user();
		$invite_code=$user->userInfo->invite_code;

		if(!empty($user->second_broker_code) || $user->hasRole('Agent')){

			//查询经纪人下属投资人
			$users = DB::table('user_info as a')
					->leftJoin('users as b', 'a.user_id', '=', 'b.id')
					->where('a.broker_code', $user->userInfo->invite_code);
					// ->where('a.invite_code', '<>', $user->userInfo->invite_code);
//liutk~
			//追加查询条件-自己的推荐客户
			if(!empty($query['invite_user']) && $query['invite_user']==$invite_code){
				$users = $users->whereNull('a.second_broker_code');
			}
			//追加查询条件-二级经纪人的推荐客户
			else if(!empty($query['invite_user'])){
				$users = $users->where('a.second_broker_code','=',$query['invite_user']);
			}
//liutk~

		}else{
			//查询二级经纪人下属投资人
			$users = DB::table('user_info as a')
					->leftJoin('users as b', 'a.user_id', '=', 'b.id')
					->where('a.second_broker_code', $user->userInfo->invite_code);
					// ->where('a.invite_code', '<>', $user->userInfo->invite_code);
			}

		//追加查询条件
		if(!empty($query['username'])){
			$users = $users->where('b.username', 'like','%'.$query['username'].'%');
		}

		//追加查询条件
		if(!empty($query['name'])){
			$users = $users->where('a.name','like', '%'.$query['name'].'%');
		}
//liutk~
		//查询自己下面的二级经纪人
		$second_broker=DB::table('user_info')
			->leftJoin('users','user_info.user_id', '=', 'users.id')
			->where('user_info.broker_code','=',$user->userInfo->invite_code)
			->whereRaw('user_info.invite_code = user_info.second_broker_code')
//			->select('user_info.name','users.username','users.phone_numebr')
			->get();
		//		dd($second_broker);
//liutk~
		$users = $users->paginate(10);

		$users->appends($query);

		 $links = (new BoosstrapPageUser($users))->render();
		//TODO
		return view('web.agent_center.customer_management',compact('links','users','query','second_broker','invite_code'));
	}

	/**
	 * 经济人管理
	 */
	public function nextAgent(Request $request){
		$user = User::user();
		$invite_next_agent_url='https://'.$_SERVER['HTTP_HOST'].'/seondRegister/'.$user->userInfo->invite_code;//二级经济人注册链接

		$A='';$B='';
		$query = $request->only('next_agent_name', 'next_agent_code');
		if(!empty($query['next_agent_name'])){
			$A="and ( `users`.`username` ='".$query['next_agent_name']."' or `users`.`phone_number`='".$query['next_agent_name']."' or `user_info` .`name`='".$query['next_agent_name']."')" ;
		}
		if(!empty($query['next_agent_code'])){
			$B="and `user_info`.`invite_code` ='".$query['next_agent_code']."'" ;
		}

		$list=DB::select("SELECT a.username,a.username,a.phone_number,a.name,a.invite_code,a.created_at,b.valid_num,c.use_money,d.invite_money,e.return_money FROM
		(SELECT `user_info`.`created_at`,`user_info`.`invite_code`,`users`.`username`,`users`.`phone_number`,`user_info`.`name`FROM `user_info` LEFT JOIN `users` on `user_info` .`user_id`=`users`.`id` WHERE `user_info`.`invite_code`=`user_info`.`second_broker_code` AND `user_info`.`broker_code` = '".$user->userInfo->invite_code."' '".$A."' '".$B."') a LEFT JOIN
		(SELECT COUNT(g.user_id)  AS 'valid_num', g.`second_broker_code` FROM (select `project_profits`.*, `user_info`.`second_broker_code`from `project_profits` LEFT JOIN `user_info` ON `user_info`.`user_id`=`project_profits`.`user_id` WHERE `project_profits`.`type`=1  GROUP BY `project_profits`.user_id ) g GROUP BY g.`second_broker_code`) b ON a.invite_code=b.second_broker_code LEFT JOIN
		(SELECT SUM(money) AS use_money, `user_info`.`second_broker_code` FROM `project_profits` LEFT JOIN `user_info` on `user_info` .`user_id`=`project_profits` .`user_id` where `project_profits` .`type`='1' GROUP BY `user_info`.`second_broker_code`) c ON a.invite_code=c.second_broker_code LEFT JOIN
		(SELECT SUM(money) AS invite_money,`projects_user`.`second_broker_code` FROM `project_profits` LEFT JOIN `projects_user`  on `projects_user`.`id` =`project_profits`.`project_user_id`WHERE  `project_profits`.`type` =2 AND `project_profits`.`role_slug` ='user' GROUP BY `projects_user`.second_broker_code ) d ON a.invite_code=d.second_broker_code LEFT JOIN
		(SELECT SUM(money) AS return_money, `projects_user`.`second_broker_code` from `project_profits` LEFT JOIN `projects_user` on `projects_user` .`id`=`project_profits`.`project_user_id` where `project_profits`.`type` =2 AND `project_profits`.`role_slug` ='agent' GROUP BY `projects_user`.`second_broker_code`) e ON a.invite_code=e.second_broker_code
		");

		$all['use_money']=collect($list)->sum('use_money');
		$all['invite_money']=collect($list)->sum('invite_money');
		$all['return_money']=collect($list)->sum('return_money');
		$page=$request->page;
		if(empty($page)){
			$page=1;
		}
		$start=($page-1)*8;
		$r_end_list=collect($list)->sortByDesc('created_at')->slice($start,8);
		$r_lists=new LengthAwarePaginator($r_end_list,count($list),8);
		$r_lists->setPath('/agent/nextAgent');
		if (!empty($query)) {
			$r_lists->appends($query);
		}
		$links = (new BoosstrapPageUser($r_lists))->render();
		$lists=$r_lists->items();

		return view('web.agent_center.user_agent',compact('lists','query','links','invite_next_agent_url','all'));

	}
	/**
	 * 二级经纪人详情
	 */
	public function nextAgentUser(Request $request){
		$second_broker_code=$request->second_broker_code;
		$second_broker=User::leftJoin('user_info','user_info.user_id','=','users.id')->where('user_info.invite_code','=',$second_broker_code)->first();

		$list=DB::select("SELECT a.username,a.user_id,a.phone_number,a.name,a.created_at,b.use_money,c.invite_money,d.return_money FROM
		(SELECT `user_info`.`created_at`,`user_info`.`user_id`,`users`.`username`,`users`.`phone_number`,`user_info`.`name`FROM `user_info` LEFT JOIN `users` on `user_info` .`user_id`=`users`.`id` WHERE `user_info`.`second_broker_code` = '".$second_broker->userInfo->invite_code."' ) a LEFT JOIN
		(SELECT SUM(money) AS use_money, `user_info`.`user_id` FROM `project_profits` LEFT JOIN `user_info` on `user_info` .`user_id`=`project_profits` .`user_id` where `project_profits` .`type`='1' GROUP BY `user_info`.`user_id`) b ON a.user_id=b.user_id LEFT JOIN
		(SELECT SUM(money) AS invite_money,`projects_user`.`user_id` FROM `project_profits` LEFT JOIN `projects_user`  on `projects_user`.`id` =`project_profits`.`project_user_id`WHERE  `project_profits`.`type` =2 AND `project_profits`.`role_slug` ='user' GROUP BY `projects_user`.user_id ) c ON a.user_id=c.user_id LEFT JOIN
		(SELECT SUM(money) AS return_money, `projects_user`.`user_id` from `project_profits` LEFT JOIN `projects_user` on `projects_user` .`id`=`project_profits`.`project_user_id` where `project_profits`.`type` =2 AND `project_profits`.`role_slug` ='agent' GROUP BY `projects_user`.`user_id`) d ON a.user_id=d.user_id
		");

		$all['show_name']=!empty($second_broker->name)?$second_broker->name:(!empty($second_broker->phone_number)?$second_broker->phone_number:$second_broker->username);
		$all['invite_code']=$second_broker->invite_code;
		$all['use_money']=collect($list)->sum('use_money');
		$all['invite_money']=collect($list)->sum('invite_money');
		$all['return_money']=collect($list)->sum('return_money');
		$page=$request->page;
		if(empty($page)){
			$page=1;
		}
		$start=($page-1)*8;
		$r_end_list=collect($list)->sortByDesc('created_at')->slice($start,8);
		$r_lists=new LengthAwarePaginator($r_end_list,count($list),8);
		$r_lists->setPath('/agent/nextAgentUser');
		if (!empty($query)) {
			$r_lists->appends($query);
		}
		$links = (new BoosstrapPageUser($r_lists))->render();
		$lists=$r_lists->items();

		return view('web.agent_center.user_agent_details',compact('lists','query','links','all'));
	}
	/**
	 * 收益配置
	 * 
	 * @return [type] [description]
	 */
	public function incomeDistribution(){
		//TODO
		return view('web.agent_center.income_distribution');
	}

	/**
	 * 投资人详情
	 * 
	 * @return [type] [description]
	 */
	public function customerInfo(int $id){

		$u = User::findOrFail($id);
		return view('web.agent_center.customer_info',compact('u'));
	}

	/**
	 * 查询某个用户的投资记录
	 * 
	 * @return [type] [description]
	 */
	public function customerInvestList(Request $request, int $customer_id){

		//查询用户
		$u = User::findOrFail($customer_id);

		$query = $request->only('name', 'startTime', 'endTime', 'status');

		$projects = DB::table('projects_user as a')
                    ->leftJoin('projects as b', 'a.projects_id', '=', 'b.id')
                    ->leftJoin('codes as c', 'b.status', '=', 'c.code')
                    ->select('b.id', 'b.projects_name', 'a.invest_money', 'a.expected_return', 'a.invest_time', 'b.trade_repayment_date', 'b.trade_finish_date', 'c.name', 'b.projects_file', 'b.status')
                    ->where('a.user_id', $customer_id);


       //追加查询条件
       
       	if(!empty($query['startTime']))
       		$projects = $projects->where('a.invest_time', '>=', $query['startTime']);       

   		if(!empty($query['endTime']))
       		$projects = $projects->where('a.invest_time', '>=', $query['endTime']);       

       	if(!empty($query['status']))
       		$projects = $projects->where('b.status', $query['status']);



        $data = $projects->orderBy('a.invest_time', 'desc')
                    ->paginate(6);

		$links = (new BoosstrapPageUser($data))->render();

        return view('web.agent_center.customer_invest_list',compact('data', 'links', 'query', 'u'));
	}

    /**
     * 投资列表
     */
    public function businessManager(Request $request){

    	$query = $request->only('name', 'startTime', 'endTime', 'status');
        $user = User::user();
        //TODO
        //

		if(!empty($user->second_broker_code) || $user->hasRole('Agent')){
	        //查询已经获得的推荐费用
	        $referal_fee = DB::table('projects_user as a')
	        			->leftJoin('project_profits as b', 'a.id', '=', 'b.project_user_id')
	        			->leftjoin('projects as c', 'a.projects_id', '=', 'c.id')
						->select(DB::raw('sum(b.money) as referal_fee'))
	        			->where('b.type', 2)
	        			->where('b.status', '<>', '0')
	        			->where('b.role_slug', 'Agent')
						->where('a.broker_code', $user->userInfo->invite_code);

			//查询用户总投资金额
			$invest_money = DB::table('projects_user as a')
						->leftjoin('projects as b', 'a.projects_id', '=', 'b.id')
						->where('a.broker_code', $user->userInfo->invite_code)
						->select(DB::raw('sum(a.invest_money) as invest_money'));



			$projects = DB::table('projects_user as a')
						->leftjoin(DB::raw("(select * from project_profits where type = 2 and role_slug = 'Agent') as b "),'a.id', '=', 'b.project_user_id')
						->leftJoin('projects as c', 'a.projects_id', '=', 'c.id')
						->leftJoin('codes as d', 'c.status', '=', 'd.code')
						->select('c.projects_name','c.id', 'a.user_name', 'a.invest_time', 'c.interest_at', 'c.end_time', 'a.invest_money', 'b.money as referal_fee', 'd.name','c.trade_repayment_date','c.trade_finish_date')
						->where('a.broker_code', $user->userInfo->invite_code);

		}else{
	        //查询已经获得的推荐费用
	        $referal_fee = DB::table('projects_user as a')
	        			->leftJoin('project_profits as b', 'a.id', '=', 'b.project_user_id')
	        			->leftjoin('projects as c', 'a.projects_id', '=', 'c.id')
						->select(DB::raw('sum(b.money) as referal_fee'))
	        			->where('b.type', 2)
	        			->where('b.status', '<>', '0')
	        			->where('b.role_slug', 'User')
						->where('a.second_broker_code', $user->userInfo->invite_code);

			//查询用户总投资金额
			$invest_money = DB::table('projects_user as a')
						->leftjoin('projects as b', 'a.projects_id', '=', 'b.id')
						->where('a.second_broker_code', $user->userInfo->invite_code)
						->select(DB::raw('sum(a.invest_money) as invest_money'));



			$projects = DB::table('projects_user as a')
						->leftjoin(DB::raw("(select * from project_profits where type = 2 and role_slug = 'User') as b "),'a.id', '=', 'b.project_user_id')
						->leftJoin('projects as c', 'a.projects_id', '=', 'c.id')
						->leftJoin('codes as d', 'c.status', '=', 'd.code')
						->select('c.projects_name','c.id', 'a.user_name', 'a.invest_time', 'c.interest_at', 'c.end_time', 'a.invest_money', 'b.money as referal_fee', 'd.name','c.trade_repayment_date','c.trade_finish_date')
						->where('a.second_broker_code', $user->userInfo->invite_code);
		}


    	//添加查询条件
    	if(!empty($query['name'])){
    		$projects = $projects->where('a.user_name', 'like', '%'.$query['name'].'%');
    		$referal_fee = $referal_fee->where('a.user_name', 'like', '%'.$query['name'].'%');
    		$invest_money = $invest_money->where('a.user_name', 'like', '%'.$query['name'].'%');
    	}

    	if(!empty($query['startTime'])){
    		$projects = $projects->where('a.invest_time', '>=' , $query['startTime']);
    		$referal_fee = $referal_fee->where('a.invest_time', '>=' , $query['startTime']);
    		$invest_money = $invest_money->where('a.invest_time', '>=' , $query['startTime']);
    	}

    	if(!empty($query['endTime'])){
    		$projects = $projects->where('a.invest_time', '<=', $query['endTime']);
    		$referal_fee = $referal_fee->where('a.invest_time', '<=', $query['endTime']);
    		$invest_money = $invest_money->where('a.invest_time', '<=', $query['endTime']);
    	}

    	if(!empty($query['status'])){
    		$projects = $projects->where('c.status', $query['status']);
    		$referal_fee = $referal_fee->where('c.status', $query['status']);
    		$invest_money = $invest_money->where('b.status', $query['status']);
    	}


		 $referal_fee =  $referal_fee->first()->referal_fee;
		 $invest_money =  $invest_money->first()->invest_money;

         $projects = $projects->orderBy('a.invest_time','desc')->paginate(6);

         $projects->appends($query);

		 $links = (new BoosstrapPageUser($projects))->render();
        //查询用户投资记录
        return view('web.agent_center.business_manager',compact( 'links','projects', 'query', 'referal_fee', 'invest_money'));
    }
}